package com.spoto.oa.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.spoto.oa.dao.CustomerDAO;
import com.spoto.oa.model.Customer;
import com.spoto.oa.util.DB;
/**
 * 
 * @Project: OA
 * @Description: 客户表DAO的具体实现
 * @Author: LinYiSong
 * @Vesion: 
 * @Date: 2010-10-24~2010-10-24
 */
public class CustomerDAOImpl implements CustomerDAO {
	
	Connection con=null;
	PreparedStatement pstm;
	ResultSet rs=null;
	public void save(Customer customer) throws SQLException {
		
		if(null==con){
			con=DB.getConn();
		}
		String sql="insert into customer(name,company,creator,mobile,address,email,qq,msn,level,others) "+
		"values(?,?,?,?,?,?,?,?,?,?)";
			pstm=con.prepareStatement(sql);
			pstm.setString(1,customer.getName());
			pstm.setString(2,customer.getCompany());
			pstm.setString(3,customer.getCreator());
			pstm.setString(4,customer.getMobile());
			pstm.setString(5,customer.getAddress());
			pstm.setString(6,customer.getEmail());
			pstm.setInt(7,customer.getQq());
			pstm.setString(8,customer.getMsn());
			pstm.setInt(9,customer.getLevel());
			pstm.setString(10,customer.getOthers());
			pstm.executeUpdate();
			
	}
	/**
	 * 取得所有的客户信息
	 * @return
	 */
	public List<Customer> getAll() {
		if(null==con){
			con=DB.getConn();
		}
		String sql="select id,name,company,creator,mobile,address,email,qq,msn,level,others from customer";
		try {
			pstm=con.prepareStatement(sql);
			rs=pstm.executeQuery();
			return resultSetToList(rs);
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 封装rs为list
	 * @param rs
	 * @return
	 */
	private List<Customer> resultSetToList(ResultSet rs) {
	    List<Customer> customers=new ArrayList<Customer>();
	    int have=0;
	    try {
			while(rs.next()){
				have=1;
				Customer customer=new Customer();
				customer.setId(rs.getInt("id"));
				customer.setName(rs.getString("name"));
				customer.setCompany(rs.getString("company"));
				customer.setLevel(rs.getInt("level"));
				customer.setMobile(rs.getString("mobile"));
				customer.setQq(rs.getInt("qq"));
				customer.setMsn(rs.getString("msn"));
				customer.setAddress(rs.getString("address"));
				customer.setEmail(rs.getString("email"));
				customer.setCreator(rs.getString("creator"));
				customer.setOthers(rs.getString("others"));
				customers.add(customer);
			}
			if(have==1)
				return customers;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 根据客户姓名取得客户对应客户信息
	 * @param name
	 * @return
	 */
	public Customer getByName(String name) {
		if(null==con){
			con=DB.getConn();
		}
		String sql="select id,name,company,creator,mobile,address,email,qq,msn,level,others from customer where name=?";
			try {
				pstm=con.prepareStatement(sql);
				pstm.setString(1, name);
				rs=pstm.executeQuery();
					return resultSetToList(rs).get(0);
			} catch (SQLException e) {
				System.out.println("取对应客户信息时出错");
				e.printStackTrace();
			}catch(java.lang.NullPointerException e){
				return null;
			}
		return null;
	}
	/**
	 * 修改客户信息
	 * @param customer
	 */
	public void update(Customer customer) {
		if(null==con){
			con=DB.getConn();
		}
		String sql="update customer set company=?,creator=?,mobile=?,address=?,email=?,qq=?,msn=?,level=?,others=? where name=?";
		try {
			pstm=con.prepareStatement(sql);
			pstm.setString(10,customer.getName());
			pstm.setString(1,customer.getCompany());
			System.out.println(customer.getCreator());
			pstm.setString(2,customer.getCreator());
			pstm.setString(3,customer.getMobile());
			pstm.setString(4,customer.getAddress());
			pstm.setString(5,customer.getEmail());
			pstm.setInt(6,customer.getQq());
			pstm.setString(7,customer.getMsn());
			pstm.setInt(8,customer.getLevel());
			pstm.setString(9,customer.getOthers());
			pstm.executeUpdate();
			
		} catch (SQLException e) {
			System.out.println("更新客户失败");
			e.printStackTrace();
		}
		
	}
	/**
	 * 根据客户名称删除客户信息
	 * @param name
	 */
	public void deleteByName(String name) {
		
		if(null==con){
			con=DB.getConn();
		}
		String sql="delete from customer where name=?";
		try {
			pstm=con.prepareStatement(sql);
			pstm.setString(1, name);
			pstm.executeUpdate();
		} catch (SQLException e) {
			System.out.println("删除客户失败");
			e.printStackTrace();
		}
	}
	/**
	 * 根据客户名称模糊搜索
	 * @param infor
	 * @return
	 */
	public List<Customer> getAllByName(String name) {
		if(null==con){
			con=DB.getConn();
		}
		String sql="select id,name,company,creator,mobile,address,email,qq,msn,level,others from customer where name like ?";
		try {
			pstm=con.prepareStatement(sql);
			pstm.setString(1,"%"+name+"%");
			rs=pstm.executeQuery();
			return resultSetToList(rs);
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 根据公司名称模糊搜索
	 * @param infor
	 * @return
	 */
	public List<Customer> getAllByCompany(String company) {
		if(null==con){
			con=DB.getConn();
		}
		String sql="select id,name,company,creator,mobile,address,email,qq,msn,level,others from customer where company like ?";
		try {
			pstm=con.prepareStatement(sql);
			pstm.setString(1,"%"+company+"%");
			rs=pstm.executeQuery();
			return resultSetToList(rs);
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 根据重要程度模糊搜索
	 * @param infor
	 * @return
	 */
	public List<Customer> getAllByLevel(int level) {
		if(null==con){
			con=DB.getConn();
		}
		String sql="select id,name,company,creator,mobile,address,email,qq,msn,level,others from customer where level like ?";
		try {
			pstm=con.prepareStatement(sql);
			pstm.setString(1,"%"+level+"%");
			rs=pstm.executeQuery();
			return resultSetToList(rs);
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 根据手机号码模糊搜索
	 * @param parseInt
	 * @return
	 */
	public List<Customer> getAllByMobile(int mobile) {
		if(null==con){
			con=DB.getConn();
		}
		String sql="select id,name,company,creator,mobile,address,email,qq,msn,level,others from customer where mobile like ?";
		try {
			pstm=con.prepareStatement(sql);
			pstm.setString(1,"%"+mobile+"%");
			rs=pstm.executeQuery();
			return resultSetToList(rs);
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 发生导入数据冲突时往冲突表中添加冲突客户记录
	 * @param customer
	 */
	public void saveConflict(Customer customer) {
		if(null==con){
			con=DB.getConn();
		}
		String sql="insert into customer_conflict(name,company,creator,mobile,address,email,qq,msn,level,others) "+
		"values(?,?,?,?,?,?,?,?,?,?)";
		try {
			pstm=con.prepareStatement(sql);
			pstm.setString(1,customer.getName());
			pstm.setString(2,customer.getCompany());
			pstm.setString(3,customer.getCreator());
			pstm.setString(4,customer.getMobile());
			pstm.setString(5,customer.getAddress());
			pstm.setString(6,customer.getEmail());
			pstm.setInt(7,customer.getQq());
			pstm.setString(8,customer.getMsn());
			pstm.setInt(9,customer.getLevel());
			pstm.setString(10,customer.getOthers());
			pstm.executeUpdate();
			
		} catch (SQLException e) {
			System.out.println("添加冲突客户失败");
			e.printStackTrace();
		}
	}
	/**
	 * 根据冲突名取得冲突数据
	 * @param name
	 * @return
	 */
	public Customer getByNameConfict(String name) {
		if(null==con){
			con=DB.getConn();
		}
		String sql="select id,name,company,creator,mobile,address,email,qq,msn,level,others from customer_conflict where name=?";
			try {
				pstm=con.prepareStatement(sql);
				pstm.setString(1, name);
				rs=pstm.executeQuery();
					return resultSetToList(rs).get(0);
			} catch (SQLException e) {
				System.out.println("取对应冲突客户信息时出错");
				e.printStackTrace();
			}
		return null;
	}
	/**
	 * 根据冲突名删除冲突数据
	 * @param customer
	 */
	public void deleteConfictByName(String name) {
		if(null==con){
			con=DB.getConn();
		}
		String sql="delete from customer_conflict where name=?";
		try {
			pstm=con.prepareStatement(sql);
			pstm.setString(1, name);
			pstm.executeUpdate();
		} catch (SQLException e) {
			System.out.println("删除客户失败");
			e.printStackTrace();
		}
	}
	/**
	 * 取得所有的冲突数据
	 * @return
	 */
	public List<Customer> getAllConfict() {
		if(null==con){
			con=DB.getConn();
		}
		String sql="select id,name,company,creator,mobile,address,email,qq,msn,level,others from customer_conflict";
		try {
			pstm=con.prepareStatement(sql);
			rs=pstm.executeQuery();
			return resultSetToList(rs);
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		return null;
	}
}
